﻿/****** Object:  ForeignKey [FK_Lit_Operario_Lit_Puesto]    Script Date: 03/24/2009 22:26:43 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Lit_Operario_Lit_Puesto]') AND type = 'F')
ALTER TABLE [dbo].[Lit_Operario] DROP CONSTRAINT [FK_Lit_Operario_Lit_Puesto]
GO
/****** Object:  ForeignKey [FK_Lit_RegistroOT_Lit_RegistroOT]    Script Date: 03/24/2009 22:26:45 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Lit_RegistroOT_Lit_RegistroOT]') AND type = 'F')
ALTER TABLE [dbo].[Lit_OrdenTrabajo] DROP CONSTRAINT [FK_Lit_RegistroOT_Lit_RegistroOT]
GO
/****** Object:  ForeignKey [FK_lit_registroOTDetalle_Lit_RegistroOT]    Script Date: 03/24/2009 22:26:50 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_lit_registroOTDetalle_Lit_RegistroOT]') AND type = 'F')
ALTER TABLE [dbo].[Lit_OTDetalle] DROP CONSTRAINT [FK_lit_registroOTDetalle_Lit_RegistroOT]
GO
/****** Object:  ForeignKey [FK_Lit_OTGasto_Lit_Gasto]    Script Date: 03/24/2009 22:26:51 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Lit_OTGasto_Lit_Gasto]') AND type = 'F')
ALTER TABLE [dbo].[Lit_OTGasto] DROP CONSTRAINT [FK_Lit_OTGasto_Lit_Gasto]
GO
/****** Object:  ForeignKey [FK_Lit_OTGasto_Lit_OrdenTrabajo]    Script Date: 03/24/2009 22:26:52 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Lit_OTGasto_Lit_OrdenTrabajo]') AND type = 'F')
ALTER TABLE [dbo].[Lit_OTGasto] DROP CONSTRAINT [FK_Lit_OTGasto_Lit_OrdenTrabajo]
GO
/****** Object:  ForeignKey [FK_lit_privilegio_lit_aplicacion]    Script Date: 03/24/2009 22:26:57 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_lit_privilegio_lit_aplicacion]') AND type = 'F')
ALTER TABLE [dbo].[lit_privilegio] DROP CONSTRAINT [FK_lit_privilegio_lit_aplicacion]
GO
/****** Object:  ForeignKey [FK_lit_privilegio_lit_privilegio]    Script Date: 03/24/2009 22:26:57 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_lit_privilegio_lit_privilegio]') AND type = 'F')
ALTER TABLE [dbo].[lit_privilegio] DROP CONSTRAINT [FK_lit_privilegio_lit_privilegio]
GO
/****** Object:  ForeignKey [FK_lit_rol_lit_aplicacion]    Script Date: 03/24/2009 22:27:00 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_lit_rol_lit_aplicacion]') AND type = 'F')
ALTER TABLE [dbo].[lit_rol] DROP CONSTRAINT [FK_lit_rol_lit_aplicacion]
GO
/****** Object:  ForeignKey [FK_lit_rol_privilegio_lit_privilegio]    Script Date: 03/24/2009 22:27:01 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_lit_rol_privilegio_lit_privilegio]') AND type = 'F')
ALTER TABLE [dbo].[lit_rol_privilegio] DROP CONSTRAINT [FK_lit_rol_privilegio_lit_privilegio]
GO
/****** Object:  ForeignKey [FK_lit_rol_privilegio_lit_rol]    Script Date: 03/24/2009 22:27:01 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_lit_rol_privilegio_lit_rol]') AND type = 'F')
ALTER TABLE [dbo].[lit_rol_privilegio] DROP CONSTRAINT [FK_lit_rol_privilegio_lit_rol]
GO
/****** Object:  ForeignKey [FK_lit_usuario_lit_aplicacion]    Script Date: 03/24/2009 22:27:03 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_lit_usuario_lit_aplicacion]') AND type = 'F')
ALTER TABLE [dbo].[lit_usuario] DROP CONSTRAINT [FK_lit_usuario_lit_aplicacion]
GO
/****** Object:  ForeignKey [FK_lit_usuario_lit_usuario]    Script Date: 03/24/2009 22:27:03 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_lit_usuario_lit_usuario]') AND type = 'F')
ALTER TABLE [dbo].[lit_usuario] DROP CONSTRAINT [FK_lit_usuario_lit_usuario]
GO
/****** Object:  ForeignKey [FK_lit_usuario_rol_lit_rol]    Script Date: 03/24/2009 22:27:04 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_lit_usuario_rol_lit_rol]') AND type = 'F')
ALTER TABLE [dbo].[lit_usuario_rol] DROP CONSTRAINT [FK_lit_usuario_rol_lit_rol]
GO
/****** Object:  ForeignKey [FK_lit_usuario_rol_lit_usuario]    Script Date: 03/24/2009 22:27:04 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_lit_usuario_rol_lit_usuario]') AND type = 'F')
ALTER TABLE [dbo].[lit_usuario_rol] DROP CONSTRAINT [FK_lit_usuario_rol_lit_usuario]
GO
/****** Object:  Table [dbo].[lit_usuario]    Script Date: 03/24/2009 22:27:02 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[lit_usuario]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[lit_usuario]
GO
/****** Object:  Table [dbo].[lit_rol]    Script Date: 03/24/2009 22:27:00 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[lit_rol]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[lit_rol]
GO
/****** Object:  Table [dbo].[lit_privilegio]    Script Date: 03/24/2009 22:26:57 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[lit_privilegio]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[lit_privilegio]
GO
/****** Object:  Table [dbo].[Lit_OrdenTrabajo]    Script Date: 03/24/2009 22:26:45 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Lit_OrdenTrabajo]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Lit_OrdenTrabajo]
GO
/****** Object:  Table [dbo].[Lit_Puesto]    Script Date: 03/24/2009 22:26:58 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Lit_Puesto]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Lit_Puesto]
GO
/****** Object:  Table [dbo].[lit_aplicacion]    Script Date: 03/24/2009 22:26:39 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[lit_aplicacion]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[lit_aplicacion]
GO
/****** Object:  Table [dbo].[lit_rol_privilegio]    Script Date: 03/24/2009 22:27:01 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[lit_rol_privilegio]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[lit_rol_privilegio]
GO
/****** Object:  Table [dbo].[lit_usuario_rol]    Script Date: 03/24/2009 22:27:03 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[lit_usuario_rol]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[lit_usuario_rol]
GO
/****** Object:  Table [dbo].[Lit_OTGasto]    Script Date: 03/24/2009 22:26:51 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Lit_OTGasto]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Lit_OTGasto]
GO
/****** Object:  Table [dbo].[Lit_OTMaterial]    Script Date: 03/24/2009 22:26:54 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Lit_OTMaterial]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Lit_OTMaterial]
GO
/****** Object:  Table [dbo].[Lit_OTDetalle]    Script Date: 03/24/2009 22:26:50 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Lit_OTDetalle]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Lit_OTDetalle]
GO
/****** Object:  Table [dbo].[Lit_Maquina]    Script Date: 03/24/2009 22:26:41 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Lit_Maquina]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Lit_Maquina]
GO
/****** Object:  Table [dbo].[Lit_Operario]    Script Date: 03/24/2009 22:26:43 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Lit_Operario]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Lit_Operario]
GO
/****** Object:  Table [dbo].[Lit_ParametrosBodega]    Script Date: 03/24/2009 22:26:55 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Lit_ParametrosBodega]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Lit_ParametrosBodega]
GO
/****** Object:  Table [dbo].[Lit_Gasto]    Script Date: 03/24/2009 22:26:40 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Lit_Gasto]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Lit_Gasto]
GO
/****** Object:  Table [dbo].[Lit_Gasto]    Script Date: 03/24/2009 22:26:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Lit_Gasto]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Lit_Gasto](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[EmpresaId] [int] NOT NULL,
	[Concepto] [varchar](50) NOT NULL,
	[Monto] [money] NOT NULL,
	[Tipo] [char](1) NOT NULL,
	[Activo] [bit] NULL,
 CONSTRAINT [PK_Litografia_Gasto] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Lit_OrdenTrabajo]    Script Date: 03/24/2009 22:26:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Lit_OrdenTrabajo]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Lit_OrdenTrabajo](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[EmpresaId] [int] NULL,
	[FechaInicial] [datetime] NULL,
	[FechaFinal] [datetime] NULL,
	[NumeroOT] [varchar](30) NULL,
	[ClienteId] [varchar](12) NULL,
	[ArticuloId] [varchar](13) NULL,
	[Unidades] [decimal](10, 2) NULL,
	[ManoObraIndirecta] [decimal](10, 2) NULL,
	[Estado] [char](1) NULL,
 CONSTRAINT [PK_Lit_RegistroOT] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Lit_Maquina]    Script Date: 03/24/2009 22:26:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Lit_Maquina]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Lit_Maquina](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[EmpresaId] [int] NOT NULL,
	[Nombre] [varchar](100) NOT NULL,
	[CostoxHora] [money] NOT NULL,
 CONSTRAINT [PK_Lit_Maquina] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Lit_Puesto]    Script Date: 03/24/2009 22:26:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Lit_Puesto]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Lit_Puesto](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Nombre] [varchar](50) NOT NULL,
	[Estado] [char](1) NOT NULL,
 CONSTRAINT [PK_Lit_Puesto] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Lit_ParametrosBodega]    Script Date: 03/24/2009 22:26:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Lit_ParametrosBodega]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Lit_ParametrosBodega](
	[EmpresaId] [int] NOT NULL,
	[SucursalId] [int] NOT NULL,
	[BodegaMateriaPrima] [int] NOT NULL,
	[BodegaProductoEnProceso] [int] NOT NULL,
	[BodegaProductoTerminado] [int] NOT NULL,
 CONSTRAINT [PK_Lit_ParametrosBodega] PRIMARY KEY CLUSTERED 
(
	[EmpresaId] ASC,
	[SucursalId] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[lit_aplicacion]    Script Date: 03/24/2009 22:26:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[lit_aplicacion]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[lit_aplicacion](
	[lit_aplicacion_id] [int] IDENTITY(1,1) NOT NULL,
	[lit_aplicacion_nombre] [varchar](50) NOT NULL,
	[lit_aplicacion_desc] [varchar](255) NOT NULL,
	[lit_empresa_id] [int] NOT NULL,
 CONSTRAINT [PK_lit_aplicacion] PRIMARY KEY CLUSTERED 
(
	[lit_aplicacion_id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Lit_OTMaterial]    Script Date: 03/24/2009 22:26:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Lit_OTMaterial]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Lit_OTMaterial](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[OTId] [int] NULL,
	[Fecha] [datetime] NULL,
	[ArticuloId] [varchar](13) NULL,
	[Tipo] [char](1) NULL,
	[Cantidad] [decimal](10, 2) NULL,
	[OperarioId] [int] NULL,
	[Departamento] [varchar](100) NULL,
	[CostoArticuloUnitario] [money] NULL,
	[CostoArticuloTotal] [money] NULL,
 CONSTRAINT [PK_Lit_RegistroOTMaterial] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Lit_OTGasto]    Script Date: 03/24/2009 22:26:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Lit_OTGasto]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Lit_OTGasto](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[OTId] [int] NULL,
	[GastoId] [int] NULL,
	[Monto] [decimal](10, 2) NULL,
 CONSTRAINT [PK_Lit_OTGasto] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[Lit_OTDetalle]    Script Date: 03/24/2009 22:26:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Lit_OTDetalle]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Lit_OTDetalle](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[OTId] [int] NULL,
	[Fecha] [datetime] NULL,
	[OperarioId] [int] NULL,
	[AyudanteId] [int] NULL,
	[MaquinaId] [int] NULL,
	[Cantidad] [decimal](10, 2) NULL,
	[HorasRegular] [decimal](10, 2) NULL,
	[HorasExtra] [decimal](10, 2) NULL,
	[HorasDoble] [decimal](10, 2) NULL,
	[Tipo] [char](1) NULL,
	[Descripcion] [varchar](200) NULL,
	[CostoAyudantexHora] [money] NULL,
	[CostoOperarioxHora] [money] NULL,
	[CostoHorasRegular] [money] NULL,
	[CostoHorasExtra] [money] NULL,
	[CostoHorasDoble] [money] NULL,
	[CostoMaquinaxHora] [money] NULL,
	[CostoMaquina] [money] NULL,
 CONSTRAINT [PK_lit_registroOTDetalle] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Lit_Operario]    Script Date: 03/24/2009 22:26:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Lit_Operario]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Lit_Operario](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[EmpresaId] [int] NOT NULL,
	[Nombre] [varchar](200) NOT NULL,
	[PuestoId] [int] NOT NULL,
	[CostoxHora] [money] NOT NULL,
 CONSTRAINT [PK_Lit_Operario] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[lit_privilegio]    Script Date: 03/24/2009 22:26:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[lit_privilegio]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[lit_privilegio](
	[lit_privilegio_id] [int] IDENTITY(1,1) NOT NULL,
	[lit_privilegio_nombre] [varchar](50) NOT NULL,
	[lit_privilegio_desc] [varchar](250) NOT NULL,
	[lit_privilegio_padre_id] [int] NULL,
	[lit_privilegio_tipo] [varchar](50) NOT NULL,
	[lit_aplicacion_id] [int] NOT NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
	[lit_privilegio_id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[lit_rol]    Script Date: 03/24/2009 22:27:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[lit_rol]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[lit_rol](
	[lit_rol_id] [int] IDENTITY(1,1) NOT NULL,
	[lit_rol_nombre] [varchar](50) NOT NULL,
	[lit_rol_desc] [varchar](255) NOT NULL,
	[lit_aplicacion_id] [int] NOT NULL,
 CONSTRAINT [PK_lit_rol] PRIMARY KEY CLUSTERED 
(
	[lit_rol_id] ASC
) ON [PRIMARY],
 CONSTRAINT [UK_lit_rol] UNIQUE NONCLUSTERED 
(
	[lit_rol_nombre] ASC,
	[lit_aplicacion_id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[lit_usuario]    Script Date: 03/24/2009 22:27:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[lit_usuario]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[lit_usuario](
	[lit_usuario_id] [int] IDENTITY(1,1) NOT NULL,
	[lit_usuario_username] [varchar](50) NOT NULL,
	[lit_usuario_password] [varchar](50) NOT NULL,
	[lit_usuario_nombre] [varchar](50) NULL,
	[lit_usuario_apellido] [varchar](50) NULL,
	[lit_aplicacion_id] [int] NOT NULL,
 CONSTRAINT [PK_lit_usuario] PRIMARY KEY CLUSTERED 
(
	[lit_usuario_id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[lit_rol_privilegio]    Script Date: 03/24/2009 22:27:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[lit_rol_privilegio]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[lit_rol_privilegio](
	[lit_rol_id] [int] NOT NULL,
	[lit_privilegio_id] [int] NOT NULL,
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
(
	[lit_rol_id] ASC,
	[lit_privilegio_id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[lit_usuario_rol]    Script Date: 03/24/2009 22:27:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[lit_usuario_rol]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[lit_usuario_rol](
	[lit_usuario_id] [int] NOT NULL,
	[lit_rol_id] [int] NOT NULL,
 CONSTRAINT [PK_lit_usuario_rol] PRIMARY KEY CLUSTERED 
(
	[lit_usuario_id] ASC,
	[lit_rol_id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object:  ForeignKey [FK_Lit_Operario_Lit_Puesto]    Script Date: 03/24/2009 22:26:43 ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Lit_Operario_Lit_Puesto]') AND type = 'F')
ALTER TABLE [dbo].[Lit_Operario]  WITH CHECK ADD  CONSTRAINT [FK_Lit_Operario_Lit_Puesto] FOREIGN KEY([PuestoId])
REFERENCES [dbo].[Lit_Puesto] ([Id])
GO
ALTER TABLE [dbo].[Lit_Operario] CHECK CONSTRAINT [FK_Lit_Operario_Lit_Puesto]
GO
/****** Object:  ForeignKey [FK_Lit_RegistroOT_Lit_RegistroOT]    Script Date: 03/24/2009 22:26:45 ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Lit_RegistroOT_Lit_RegistroOT]') AND type = 'F')
ALTER TABLE [dbo].[Lit_OrdenTrabajo]  WITH CHECK ADD  CONSTRAINT [FK_Lit_RegistroOT_Lit_RegistroOT] FOREIGN KEY([Id])
REFERENCES [dbo].[Lit_OrdenTrabajo] ([Id])
GO
ALTER TABLE [dbo].[Lit_OrdenTrabajo] CHECK CONSTRAINT [FK_Lit_RegistroOT_Lit_RegistroOT]
GO
/****** Object:  ForeignKey [FK_lit_registroOTDetalle_Lit_RegistroOT]    Script Date: 03/24/2009 22:26:50 ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_lit_registroOTDetalle_Lit_RegistroOT]') AND type = 'F')
ALTER TABLE [dbo].[Lit_OTDetalle]  WITH CHECK ADD  CONSTRAINT [FK_lit_registroOTDetalle_Lit_RegistroOT] FOREIGN KEY([OTId])
REFERENCES [dbo].[Lit_OrdenTrabajo] ([Id])
GO
ALTER TABLE [dbo].[Lit_OTDetalle] CHECK CONSTRAINT [FK_lit_registroOTDetalle_Lit_RegistroOT]
GO
/****** Object:  ForeignKey [FK_Lit_OTGasto_Lit_Gasto]    Script Date: 03/24/2009 22:26:51 ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Lit_OTGasto_Lit_Gasto]') AND type = 'F')
ALTER TABLE [dbo].[Lit_OTGasto]  WITH CHECK ADD  CONSTRAINT [FK_Lit_OTGasto_Lit_Gasto] FOREIGN KEY([GastoId])
REFERENCES [dbo].[Lit_Gasto] ([Id])
GO
ALTER TABLE [dbo].[Lit_OTGasto] CHECK CONSTRAINT [FK_Lit_OTGasto_Lit_Gasto]
GO
/****** Object:  ForeignKey [FK_Lit_OTGasto_Lit_OrdenTrabajo]    Script Date: 03/24/2009 22:26:52 ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Lit_OTGasto_Lit_OrdenTrabajo]') AND type = 'F')
ALTER TABLE [dbo].[Lit_OTGasto]  WITH CHECK ADD  CONSTRAINT [FK_Lit_OTGasto_Lit_OrdenTrabajo] FOREIGN KEY([OTId])
REFERENCES [dbo].[Lit_OrdenTrabajo] ([Id])
GO
ALTER TABLE [dbo].[Lit_OTGasto] CHECK CONSTRAINT [FK_Lit_OTGasto_Lit_OrdenTrabajo]
GO
/****** Object:  ForeignKey [FK_lit_privilegio_lit_aplicacion]    Script Date: 03/24/2009 22:26:57 ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_lit_privilegio_lit_aplicacion]') AND type = 'F')
ALTER TABLE [dbo].[lit_privilegio]  WITH CHECK ADD  CONSTRAINT [FK_lit_privilegio_lit_aplicacion] FOREIGN KEY([lit_aplicacion_id])
REFERENCES [dbo].[lit_aplicacion] ([lit_aplicacion_id])
GO
ALTER TABLE [dbo].[lit_privilegio] CHECK CONSTRAINT [FK_lit_privilegio_lit_aplicacion]
GO
/****** Object:  ForeignKey [FK_lit_privilegio_lit_privilegio]    Script Date: 03/24/2009 22:26:57 ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_lit_privilegio_lit_privilegio]') AND type = 'F')
ALTER TABLE [dbo].[lit_privilegio]  WITH CHECK ADD  CONSTRAINT [FK_lit_privilegio_lit_privilegio] FOREIGN KEY([lit_privilegio_padre_id])
REFERENCES [dbo].[lit_privilegio] ([lit_privilegio_id])
GO
ALTER TABLE [dbo].[lit_privilegio] CHECK CONSTRAINT [FK_lit_privilegio_lit_privilegio]
GO
/****** Object:  ForeignKey [FK_lit_rol_lit_aplicacion]    Script Date: 03/24/2009 22:27:00 ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_lit_rol_lit_aplicacion]') AND type = 'F')
ALTER TABLE [dbo].[lit_rol]  WITH CHECK ADD  CONSTRAINT [FK_lit_rol_lit_aplicacion] FOREIGN KEY([lit_aplicacion_id])
REFERENCES [dbo].[lit_aplicacion] ([lit_aplicacion_id])
GO
ALTER TABLE [dbo].[lit_rol] CHECK CONSTRAINT [FK_lit_rol_lit_aplicacion]
GO
/****** Object:  ForeignKey [FK_lit_rol_privilegio_lit_privilegio]    Script Date: 03/24/2009 22:27:01 ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_lit_rol_privilegio_lit_privilegio]') AND type = 'F')
ALTER TABLE [dbo].[lit_rol_privilegio]  WITH CHECK ADD  CONSTRAINT [FK_lit_rol_privilegio_lit_privilegio] FOREIGN KEY([lit_privilegio_id])
REFERENCES [dbo].[lit_privilegio] ([lit_privilegio_id])
GO
ALTER TABLE [dbo].[lit_rol_privilegio] CHECK CONSTRAINT [FK_lit_rol_privilegio_lit_privilegio]
GO
/****** Object:  ForeignKey [FK_lit_rol_privilegio_lit_rol]    Script Date: 03/24/2009 22:27:01 ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_lit_rol_privilegio_lit_rol]') AND type = 'F')
ALTER TABLE [dbo].[lit_rol_privilegio]  WITH CHECK ADD  CONSTRAINT [FK_lit_rol_privilegio_lit_rol] FOREIGN KEY([lit_rol_id])
REFERENCES [dbo].[lit_rol] ([lit_rol_id])
GO
ALTER TABLE [dbo].[lit_rol_privilegio] CHECK CONSTRAINT [FK_lit_rol_privilegio_lit_rol]
GO
/****** Object:  ForeignKey [FK_lit_usuario_lit_aplicacion]    Script Date: 03/24/2009 22:27:03 ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_lit_usuario_lit_aplicacion]') AND type = 'F')
ALTER TABLE [dbo].[lit_usuario]  WITH CHECK ADD  CONSTRAINT [FK_lit_usuario_lit_aplicacion] FOREIGN KEY([lit_aplicacion_id])
REFERENCES [dbo].[lit_aplicacion] ([lit_aplicacion_id])
GO
ALTER TABLE [dbo].[lit_usuario] CHECK CONSTRAINT [FK_lit_usuario_lit_aplicacion]
GO
/****** Object:  ForeignKey [FK_lit_usuario_lit_usuario]    Script Date: 03/24/2009 22:27:03 ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_lit_usuario_lit_usuario]') AND type = 'F')
ALTER TABLE [dbo].[lit_usuario]  WITH CHECK ADD  CONSTRAINT [FK_lit_usuario_lit_usuario] FOREIGN KEY([lit_usuario_id])
REFERENCES [dbo].[lit_usuario] ([lit_usuario_id])
GO
ALTER TABLE [dbo].[lit_usuario] CHECK CONSTRAINT [FK_lit_usuario_lit_usuario]
GO
/****** Object:  ForeignKey [FK_lit_usuario_rol_lit_rol]    Script Date: 03/24/2009 22:27:04 ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_lit_usuario_rol_lit_rol]') AND type = 'F')
ALTER TABLE [dbo].[lit_usuario_rol]  WITH CHECK ADD  CONSTRAINT [FK_lit_usuario_rol_lit_rol] FOREIGN KEY([lit_rol_id])
REFERENCES [dbo].[lit_rol] ([lit_rol_id])
GO
ALTER TABLE [dbo].[lit_usuario_rol] CHECK CONSTRAINT [FK_lit_usuario_rol_lit_rol]
GO
/****** Object:  ForeignKey [FK_lit_usuario_rol_lit_usuario]    Script Date: 03/24/2009 22:27:04 ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_lit_usuario_rol_lit_usuario]') AND type = 'F')
ALTER TABLE [dbo].[lit_usuario_rol]  WITH CHECK ADD  CONSTRAINT [FK_lit_usuario_rol_lit_usuario] FOREIGN KEY([lit_usuario_id])
REFERENCES [dbo].[lit_usuario] ([lit_usuario_id])
GO
ALTER TABLE [dbo].[lit_usuario_rol] CHECK CONSTRAINT [FK_lit_usuario_rol_lit_usuario]
GO

/****** Object:  StoredProcedure [dbo].[companyGetByAppName]    Script Date: 03/24/2009 22:35:09 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[companyGetByAppName]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[companyGetByAppName]
GO
/****** Object:  StoredProcedure [dbo].[rightFindByUser]    Script Date: 03/24/2009 22:35:15 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[rightFindByUser]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[rightFindByUser]
GO
/****** Object:  StoredProcedure [dbo].[usersGetInRole]    Script Date: 03/24/2009 22:35:19 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[usersGetInRole]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[usersGetInRole]
GO
/****** Object:  StoredProcedure [dbo].[userIsInRole]    Script Date: 03/24/2009 22:35:18 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[userIsInRole]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[userIsInRole]
GO
/****** Object:  StoredProcedure [dbo].[userRoleDelete]    Script Date: 03/24/2009 22:35:19 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[userRoleDelete]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[userRoleDelete]
GO
/****** Object:  StoredProcedure [dbo].[userRoleAdd]    Script Date: 03/24/2009 22:35:19 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[userRoleAdd]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[userRoleAdd]
GO
/****** Object:  StoredProcedure [dbo].[roleFindByUser]    Script Date: 03/24/2009 22:35:16 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[roleFindByUser]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[roleFindByUser]
GO
/****** Object:  StoredProcedure [dbo].[userFindInRole]    Script Date: 03/24/2009 22:35:18 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[userFindInRole]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[userFindInRole]
GO
/****** Object:  StoredProcedure [dbo].[userFindByName]    Script Date: 03/24/2009 22:35:18 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[userFindByName]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[userFindByName]
GO
/****** Object:  StoredProcedure [dbo].[roleDelete]    Script Date: 03/24/2009 22:35:16 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[roleDelete]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[roleDelete]
GO
/****** Object:  StoredProcedure [dbo].[rightFindByRole]    Script Date: 03/24/2009 22:35:14 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[rightFindByRole]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[rightFindByRole]
GO
/****** Object:  StoredProcedure [dbo].[roleFindByName]    Script Date: 03/24/2009 22:35:16 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[roleFindByName]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[roleFindByName]
GO
/****** Object:  StoredProcedure [dbo].[roleFindAll]    Script Date: 03/24/2009 22:35:16 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[roleFindAll]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[roleFindAll]
GO
/****** Object:  StoredProcedure [dbo].[rightFindByType]    Script Date: 03/24/2009 22:35:15 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[rightFindByType]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[rightFindByType]
GO
/****** Object:  StoredProcedure [dbo].[roleAdd]    Script Date: 03/24/2009 22:35:16 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[roleAdd]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[roleAdd]
GO
/****** Object:  StoredProcedure [dbo].[RegistroOTGastoSave]    Script Date: 03/24/2009 22:35:13 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[RegistroOTGastoSave]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[RegistroOTGastoSave]
GO
/****** Object:  StoredProcedure [dbo].[userRoleAddWithIds]    Script Date: 03/24/2009 22:35:19 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[userRoleAddWithIds]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[userRoleAddWithIds]
GO
/****** Object:  StoredProcedure [dbo].[rightToRoleAdd]    Script Date: 03/24/2009 22:35:15 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[rightToRoleAdd]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[rightToRoleAdd]
GO
/****** Object:  StoredProcedure [dbo].[userAdd]    Script Date: 03/24/2009 22:35:17 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[userAdd]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[userAdd]
GO
/****** Object:  StoredProcedure [dbo].[roleUpdate]    Script Date: 03/24/2009 22:35:17 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[roleUpdate]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[roleUpdate]
GO
/****** Object:  StoredProcedure [dbo].[userUpdate]    Script Date: 03/24/2009 22:35:20 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[userUpdate]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[userUpdate]
GO
/****** Object:  StoredProcedure [dbo].[userDelete]    Script Date: 03/24/2009 22:35:17 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[userDelete]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[userDelete]
GO
/****** Object:  StoredProcedure [dbo].[rightFromRoleDelete]    Script Date: 03/24/2009 22:35:15 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[rightFromRoleDelete]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[rightFromRoleDelete]
GO
/****** Object:  StoredProcedure [dbo].[rolesFromUserDelete]    Script Date: 03/24/2009 22:35:17 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[rolesFromUserDelete]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[rolesFromUserDelete]
GO
/****** Object:  StoredProcedure [dbo].[userFindAll]    Script Date: 03/24/2009 22:35:18 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[userFindAll]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[userFindAll]
GO
/****** Object:  StoredProcedure [dbo].[RegistroOTSave]    Script Date: 03/24/2009 22:35:14 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[RegistroOTSave]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[RegistroOTSave]
GO
/****** Object:  StoredProcedure [dbo].[GastoAdd]    Script Date: 03/24/2009 22:35:10 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[GastoAdd]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[GastoAdd]
GO
/****** Object:  StoredProcedure [dbo].[GastoUpdate]    Script Date: 03/24/2009 22:35:10 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[GastoUpdate]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[GastoUpdate]
GO
/****** Object:  StoredProcedure [dbo].[GastoDelete]    Script Date: 03/24/2009 22:35:10 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[GastoDelete]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[GastoDelete]
GO
/****** Object:  StoredProcedure [dbo].[RegistroOTTerminate]    Script Date: 03/24/2009 22:35:14 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[RegistroOTTerminate]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[RegistroOTTerminate]
GO
/****** Object:  StoredProcedure [dbo].[RegistroOTDetalleSave]    Script Date: 03/24/2009 22:35:13 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[RegistroOTDetalleSave]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[RegistroOTDetalleSave]
GO
/****** Object:  StoredProcedure [dbo].[OperarioGetList]    Script Date: 03/24/2009 22:35:11 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[OperarioGetList]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[OperarioGetList]
GO
/****** Object:  StoredProcedure [dbo].[HorasLaboradasGet]    Script Date: 03/24/2009 22:35:11 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[HorasLaboradasGet]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[HorasLaboradasGet]
GO
/****** Object:  StoredProcedure [dbo].[applicationGetId]    Script Date: 03/24/2009 22:35:09 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[applicationGetId]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[applicationGetId]
GO
/****** Object:  StoredProcedure [dbo].[OrdenTrabajoParametersGet]    Script Date: 03/24/2009 22:35:11 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[OrdenTrabajoParametersGet]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[OrdenTrabajoParametersGet]
GO
/****** Object:  StoredProcedure [dbo].[RegistroOTGet]    Script Date: 03/24/2009 22:35:13 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[RegistroOTGet]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[RegistroOTGet]
GO
/****** Object:  StoredProcedure [dbo].[OrdenTrabajoSearch]    Script Date: 03/24/2009 22:35:12 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[OrdenTrabajoSearch]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[OrdenTrabajoSearch]
GO
/****** Object:  StoredProcedure [dbo].[ProductoGetList]    Script Date: 03/24/2009 22:35:12 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[ProductoGetList]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[ProductoGetList]
GO
/****** Object:  StoredProcedure [dbo].[RegistroOTDetalleRep]    Script Date: 03/24/2009 22:35:12 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[RegistroOTDetalleRep]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[RegistroOTDetalleRep]
GO
/****** Object:  StoredProcedure [dbo].[RegistroOTMaterialSave]    Script Date: 03/24/2009 22:35:14 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[RegistroOTMaterialSave]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[RegistroOTMaterialSave]
GO
/****** Object:  StoredProcedure [dbo].[ClienteGetList]    Script Date: 03/24/2009 22:35:09 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[ClienteGetList]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[ClienteGetList]
GO
/****** Object:  StoredProcedure [dbo].[branchGetByCompany]    Script Date: 03/24/2009 22:35:09 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[branchGetByCompany]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[branchGetByCompany]
GO
/****** Object:  StoredProcedure [dbo].[GastoGet]    Script Date: 03/24/2009 22:35:10 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[GastoGet]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[GastoGet]
GO
/****** Object:  StoredProcedure [dbo].[GastoGet]    Script Date: 03/24/2009 22:35:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[GastoGet]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GastoGet] 	
	(	
		@empresaId AS INT,
		@tipo AS CHAR(1) = NULL
	)	
AS
BEGIN	
	SET NOCOUNT ON
	  
	SELECT Id, Concepto, Monto, Tipo 
	FROM Lit_Gasto
	WHERE EmpresaId = @empresaId
	  AND Activo = 1	
	  AND Tipo = ISNULL(@tipo,Tipo)
	  
	SET NOCOUNT OFF
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[GastoAdd]    Script Date: 03/24/2009 22:35:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[GastoAdd]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GastoAdd] 	
	(	
		@empresaId AS INT,
		@concepto AS VARCHAR(50),
		@monto AS MONEY,
		@tipo AS CHAR(1)
	)	
AS
BEGIN	
	SET NOCOUNT ON
	
	INSERT INTO Lit_Gasto (empresaId, concepto, monto, tipo, activo)
	VALUES (@empresaId, @concepto, @monto, @tipo, 1)  
		
	SET NOCOUNT OFF
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[GastoUpdate]    Script Date: 03/24/2009 22:35:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[GastoUpdate]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GastoUpdate] 	
	(	
		@id AS INT,
		@concepto AS VARCHAR(50),
		@monto AS MONEY,
		@tipo AS CHAR(1)
	)	
AS
BEGIN	
	SET NOCOUNT ON
	  
	UPDATE	Lit_Gasto
	SET		Concepto = @concepto,
			Monto = @monto,
			Tipo = @tipo	
	WHERE id = @id
	
	SET NOCOUNT OFF
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[GastoDelete]    Script Date: 03/24/2009 22:35:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[GastoDelete]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GastoDelete] 	
	(	
		@id AS INT
	)	
AS
BEGIN	
	SET NOCOUNT ON
	  
	UPDATE	Lit_Gasto
	SET		Activo = 0			
	WHERE id = @id
	
	SET NOCOUNT OFF
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[RegistroOTDetalleRep]    Script Date: 03/24/2009 22:35:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[RegistroOTDetalleRep]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[RegistroOTDetalleRep]
	(	
		@EmpresaId	AS INT,
		@NumeroOT	AS VARCHAR(30)
	)	
AS
BEGIN	
	SET NOCOUNT ON

	DECLARE @id AS INT
	DECLARE @OTDetalle Table
		(NumeroOT		VARCHAR(30),
		 PuestoNombre	VARCHAR(50),
		 Horas			DECIMAL(10,2),
		 Total			MONEY)


	-- Lista de tablas a ser devueltas a la aplicacion para cargar la hoja de registro	
	SELECT 0 AS TableId, ''Catalog''			AS TableName	UNION
	SELECT 1 AS TableId, ''OrdenTrabajo''		AS TableName	UNION
	SELECT 2 AS TableId, ''OTDetalle''		AS TableName	UNION
	SELECT 3 AS TableId, ''OTMaterial''		AS TableName    UNION
	SELECT 4 AS TableId, ''OTGastoDirecto''	AS TableName    UNION
	SELECT 5 AS TableId, ''OTGastoIndirecto''		AS TableName    UNION
	SELECT 6 AS TableId, ''OTMaquina''		AS TableName   -- UNION		

	SELECT @id = id 
	FROM Lit_OrdenTrabajo 
	WHERE EmpresaId = @empresaId
	  AND NumeroOT = @numeroOT

	-- OrdenTrabajo
	SELECT o.NumeroOT, e.Nombre AS EmpresaNombre, FechaInicial, FechaFinal, dbo.EstadoNombreGet(o.Estado) AS Estado, o.Unidades As Cantidad, CONVERT(DECIMAL(10,2), 0) AS CantidadProducida
	FROM lit_ordenTrabajo o
	INNER JOIN ld_general..empresas e ON o.empresaId = e.id
	WHERE o.Id = @id

	-- OT Detalle (Mano de obra)
	INSERT INTO @OTDetalle
		SELECT ot.NumeroOT, p.Nombre AS PuestoNombre, SUM(ISNULL(HorasRegular,0)+ISNULL(HorasExtra,0) + ISNULL(HorasDoble,0)) AS Horas, SUM((ISNULL(HorasRegular,0)* ISNULL(CostoOperarioxHora,0))+(ISNULL(HorasExtra,0)*ISNULL(CostoOperarioxHora,0)*1.5) + (ISNULL(CostoHorasDoble,0)*ISNULL(CostoOperarioxHora,0)*2.0)) AS Total
		FROM Lit_OTDetalle o
		INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = o.OTId
		INNER JOIN Lit_Operario op ON o.operarioId = op.id
		INNER JOIN Lit_Puesto p ON op.puestoId = p.id
		WHERE OTId = @id	  
		GROUP BY ot.NumeroOT, p.Nombre
		UNION
		SELECT ot.NumeroOT, p.Nombre AS PuestoNombre, SUM(ISNULL(HorasRegular,0)+ISNULL(HorasExtra,0) + ISNULL(HorasDoble,0)) AS Horas, SUM((ISNULL(HorasRegular,0)* ISNULL(CostoAyudantexHora,0))+(ISNULL(HorasExtra,0)*ISNULL(CostoAyudantexHora,0)*1.5) + (ISNULL(CostoHorasDoble,0)*ISNULL(CostoAyudantexHora,0)*2.0)) AS Total
		FROM Lit_OTDetalle o
		INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = o.OTId
		INNER JOIN Lit_Operario op ON o.ayudanteId = op.id
		INNER JOIN Lit_Puesto p ON op.puestoId = p.id
		WHERE OTId = @id	  
		GROUP BY ot.NumeroOT, p.Nombre
	
	SELECT NumeroOT, PuestoNombre, SUM(Horas) AS Horas, SUM(Total) AS Total
	FROM @OTDetalle
	GROUP BY NumeroOT, PuestoNombre	
	
	-- OT Material
	SELECT ot.NumeroOT, m.ArticuloId, art_nombre AS ArticuloNombre, Cantidad, CostoArticuloUnitario AS Costo, SUM(CostoArticuloTotal) AS Total
	FROM Lit_OTMaterial m
	INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = m.OTId
	INNER JOIN Articulo a ON m.ArticuloId = a.Art_id
	WHERE OTId = @id
	AND a.Emp_Id = @empresaId
	GROUP BY ot.NumeroOT, m.ArticuloId, art_nombre, Cantidad, CostoArticuloUnitario

	-- OT Gastos Directos
	SELECT ot.NumeroOT, otg.GastoId, g.Concepto As GastoNombre, otg.Monto
	FROM Lit_OTGasto otg
	INNER JOIN Lit_Gasto g ON g.Id = otg.GastoId
	INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = otg.OTId
	WHERE OTId = @id AND g.Tipo = ''D''

	-- OT Gastos Indirectos
	SELECT ot.NumeroOT, otg.GastoId, g.Concepto As GastoNombre, otg.Monto
	FROM Lit_OTGasto otg
	INNER JOIN Lit_Gasto g ON g.Id = otg.GastoId
	INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = otg.OTId
	WHERE OTId = @id AND g.Tipo = ''I''
	UNION
	SELECT NumeroOT, 0 As GastoId, ''Mano de Obra Indirecta'' As GastoNombre, ISNULL(ManoObraIndirecta,0)
	FROM Lit_OrdenTrabajo
	WHERE Id = @id
	AND ManoObraIndirecta IS NOT NULL
	AND ManoObraIndirecta > 0

	-- OT Maquina
	SELECT ot.NumeroOT, o.MaquinaId, m.Nombre AS MaquinaNombre, SUM(ISNULL(HorasRegular,0)+ISNULL(HorasExtra,0) + ISNULL(HorasDoble,0)) AS Horas, o.CostoMaquinaxHora, SUM(CostoMaquina) AS Total
	FROM Lit_OTDetalle o
	INNER JOIN Lit_OrdenTrabajo ot ON ot.Id = o.OTId
	INNER JOIN Lit_Maquina m ON o.MaquinaId = m.Id
	WHERE OTId = @id	  
	GROUP BY ot.NumeroOT, o.MaquinaId, m.Nombre, o.CostoMaquinaxHora
           	            
	SET NOCOUNT OFF
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[OrdenTrabajoSearch]    Script Date: 03/24/2009 22:35:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[OrdenTrabajoSearch]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[OrdenTrabajoSearch]
	-- Add the parameters for the stored procedure here
	@work_order_number VARCHAR(30) = NULL, 
	@work_order_status VARCHAR(1) = NULL,
	@work_order_begin_date DATETIME = NULL,
	@work_order_end_date DATETIME = NULL,
	@emp_id INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT	ot.Id,
			ot.NumeroOT,
			ot.FechaInicial, 
			ot.FechaFinal, 
			ot.ClienteId, 
			c.Cliente_Nombre as ClienteNombre, 
			ot.ArticuloId, 
			a.Art_Nombre as ArticuloNombre, 
			ot.Unidades, 
			ot.Estado,
			dbo.EstadoNombreGet(ot.Estado) As EstadoNombre
	FROM Lit_OrdenTrabajo ot
	LEFT JOIN Cliente c ON ot.ClienteId = c.Cliente_Id AND ot.EmpresaId = c.Emp_Id
	LEFT JOIN Articulo a ON ot.ArticuloId = a.Art_Id AND ot.EmpresaId = a.Emp_Id
	WHERE ot.NumeroOT = ISNULL(@work_order_number, ot.NumeroOT)
	AND ot.Estado = ISNULL(@work_order_status, ot.Estado)
	AND ot.FechaInicial BETWEEN ISNULL(@work_order_begin_date, ot.FechaInicial) AND ISNULL(@work_order_end_date, ot.FechaInicial)
	AND ot.EmpresaId = @emp_id;
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[RegistroOTSave]    Script Date: 03/24/2009 22:35:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[RegistroOTSave]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[RegistroOTSave] 	
	(	
		@empresaId AS INT,
		@numeroOT AS VARCHAR(30),		
		@fechaInicial AS DATETIME,		
		@clienteId AS VARCHAR(12) = NULL,
		@articuloId AS VARCHAR(13) = NULL,
		@unidades AS DECIMAL(10,2)  = NULL,
		@manoObraIndirecta AS DECIMAL(10,2)  = NULL
	)	
AS
BEGIN	
	SET NOCOUNT ON

	DECLARE @id AS INT
	
	SELECT @id = id 
	FROM Lit_OrdenTrabajo 
	WHERE EmpresaId = @empresaId
	  AND NumeroOT = @numeroOT

	
	IF @id IS NOT NULL
	BEGIN
	
		-- Remueve todos registros asociados para recrearlos despues en los otros SPs
		DELETE FROM Lit_OTDetalle	WHERE OTId = @id
		DELETE FROM Lit_OTMaterial	WHERE OTId = @id
		DELETE FROM Lit_OTGasto		WHERE OTId = @id
		
	
		UPDATE Lit_OrdenTrabajo
		SET		FechaInicial = @fechaInicial,
				ClienteId = @clienteId,	
				ArticuloId = @articuloId,
				Unidades = @unidades,	
				ManoObraIndirecta = @manoObraIndirecta
		WHERE Id = @id
	END
	ELSE
	BEGIN		
		INSERT INTO Lit_OrdenTrabajo 
			(EmpresaId, FechaInicial, NumeroOT, ClienteId, ArticuloId, Unidades, ManoObraIndirecta, Estado)		
		VALUES 
			(@empresaId, @fechaInicial, @numeroOT, @clienteId, @articuloId, @unidades, @manoObraIndirecta, ''A'')
		
		SELECT @id = Scope_identity()
	END	

	SELECT @id
		
	SET NOCOUNT OFF
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[HorasLaboradasGet]    Script Date: 03/24/2009 22:35:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[HorasLaboradasGet]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[HorasLaboradasGet] 	
	(	
		@empresaId		AS INT,		
		@operarioLista	AS VARCHAR(4000) = NULL,
		@fechaInicial	AS DATETIME = NULL,		
		@fechaFinal		AS DATETIME = NULL		
	)	
AS
BEGIN	
	SET NOCOUNT ON
	
	-- Lista de tablas a ser devueltas a la aplicacion para cargar la hoja de registro	
	SELECT 0 AS TableId, ''Catalog''			AS TableName	UNION
	SELECT 1 AS TableId, ''Horas''			AS TableName	

	DECLARE @operarios TABLE (Id INT)
	DECLARE @result TABLE 
			(NumeroOT VARCHAR(30),
			 Operario VARCHAR(50),
			 HorasR   DECIMAL(10,2),
			 HorasE	  DECIMAL(10,2),
			 HorasD	  DECIMAL(10,2))

	DECLARE @del CHAR(1);
	SET @del='','';
	
	SET @operarioLista = @operarioLista + @del;
	
	DECLARE @value varchar(20);
	WHILE charindex(@del,@operarioLista,0) > 0
	BEGIN
		SELECT	@value  = rtrim(ltrim(substring(@operarioLista,1,charindex(@del,@operarioLista,0)-1))),
				@operarioLista = rtrim(ltrim(substring(@operarioLista,charindex(@del,@operarioLista,0)+1,len(@operarioLista))))
		IF NOT EXISTS(SELECT @value FROM @operarios WHERE id=@value)
		BEGIN
			INSERT INTO @operarios(Id) VALUES(@value)
		END
	END	

	INSERT INTO @result
		SELECT OT.NumeroOT, O.Nombre AS Operario, SUM(HorasRegular) HorasR, SUM(HorasExtra) HorasE, SUM(HorasDoble) HorasD
		FROM Lit_OTDetalle OTD
		INNER JOIN Lit_OrdenTrabajo OT ON OTD.OTId = OT.Id
		INNER JOIN Lit_Operario O ON OTD.OperarioId = O.Id	
		INNER JOIN @operarios L ON L.Id = O.Id
		WHERE OT.EmpresaId = @empresaId	  
		  AND Fecha Between ISNULL(@fechaInicial,Fecha) AND ISNULL(@fechaFinal,Fecha)	  
		GROUP BY OT.NumeroOT, O.Nombre
		UNION
		SELECT OT.NumeroOT, O.Nombre AS Operario, SUM(HorasRegular) HorasR, SUM(HorasExtra) HorasE, SUM(HorasDoble) HorasD
		FROM Lit_OTDetalle OTD
		INNER JOIN Lit_OrdenTrabajo OT ON OTD.OTId = OT.Id
		INNER JOIN Lit_Operario O ON OTD.AyudanteId = O.Id	
		INNER JOIN @operarios L ON L.Id = O.Id
		WHERE OT.EmpresaId = @empresaId	  
		  AND Fecha Between ISNULL(@fechaInicial,Fecha) AND ISNULL(@fechaFinal,Fecha)	  
		GROUP BY OT.NumeroOT, O.Nombre
	
	SELECT E.Nombre AS EmpresaNombre, @fechaInicial AS FechaInicial, @fechaFinal AS FechaFinal, NumeroOT, Operario, SUM(HorasR) HorasR, SUM(HorasE) HorasE, SUM(HorasD) HorasD, 
		   ISNULL(SUM(HorasR),0) + ISNULL(SUM(HorasE),0) + ISNULL(SUM(HorasD),0) AS HorasTotal
	FROM @result
	INNER JOIN LD_General..Empresas E ON (E.Id = @empresaId)
	GROUP BY E.Nombre, NumeroOT, Operario	

	SET NOCOUNT OFF
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[RegistroOTGet]    Script Date: 03/24/2009 22:35:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[RegistroOTGet]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[RegistroOTGet]
	(	
		@EmpresaId	AS INT,
		@NumeroOT	AS VARCHAR(30)
	)	
AS
BEGIN	
	SET NOCOUNT ON

	DECLARE @id AS INT

	-- Lista de tablas a ser devueltas a la aplicacion para cargar la hoja de registro	
	SELECT 0 AS TableId, ''Catalog''				AS TableName	UNION
	SELECT 1 AS TableId, ''OrdenTrabajo''			AS TableName	UNION
	SELECT 2 AS TableId, ''OrdenTrabajoDetalle''	AS TableName	UNION
	SELECT 3 AS TableId, ''OrdenTrabajoMaterial''	AS TableName    UNION
	SELECT 4 AS TableId, ''OrdenTrabajoGasto''	AS TableName   -- UNION		

	SELECT @id = id 
	FROM Lit_OrdenTrabajo 
	WHERE EmpresaId = @empresaId
	  AND NumeroOT = @numeroOT

	-- OrdenTrabajo
	SELECT Id, FechaInicial, FechaFinal, NumeroOT, OT.ClienteId, C.Cliente_Nombre AS ClienteNombre, ArticuloId AS ProductoId, Unidades, ManoObraIndirecta, Estado
	FROM Lit_OrdenTrabajo OT
	LEFT JOIN Cliente C ON OT.EmpresaId = C.Emp_id AND OT.ClienteId = C.Cliente_id
	LEFT JOIN Articulo A ON OT.EmpresaId = A.Emp_id AND OT.ArticuloId = A.Art_Id
	WHERE id = @id	  
		
	-- OrdenTrabajoDetalle
	SELECT	Id, OTId, Tipo, Fecha, OperarioId, AyudanteId, MaquinaId, Cantidad, 
			HorasRegular, HorasExtra, HorasDoble, Descripcion
	FROM Lit_OTDetalle
	WHERE OTId = @id
	
	-- OrdenTrabajoMaterial
	SELECT	Id, OTId, Tipo, Fecha, ArticuloId AS MaterialId, Cantidad, OperarioId, Departamento
	FROM Lit_OTMaterial
	WHERE OTId = @id
	
	-- OrdenTrabajoGasto
	SELECT	Id, OTId, GastoId, Monto
	FROM Lit_OTGasto
	WHERE OTId = @id
           	            
	SET NOCOUNT OFF
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[RegistroOTTerminate]    Script Date: 03/24/2009 22:35:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[RegistroOTTerminate]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[RegistroOTTerminate] 	
	(	
		@empresaId AS INT,
		@numeroOT AS VARCHAR(30)	
	)	
AS
BEGIN	
	SET NOCOUNT ON
	
	UPDATE Lit_OrdenTrabajo
	SET		Estado = ''T'',
			FechaFinal = GETDATE()
	WHERE EmpresaId = @empresaId
	  AND NumeroOT = @numeroOT
		
	SET NOCOUNT OFF
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[RegistroOTDetalleSave]    Script Date: 03/24/2009 22:35:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[RegistroOTDetalleSave]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[RegistroOTDetalleSave] 	
	(	
		@OTId AS INT,		
		@fecha AS DATETIME,		
		@operarioId AS INT = NULL,
		@ayudanteId AS INT = NULL,
		@maquinaId AS INT = NULL,
		@cantidad AS DECIMAL(10,2)  = NULL,
		@horasRegular AS DECIMAL(10,2)  = NULL,
		@horasExtra AS DECIMAL(10,2)  = NULL,
		@horasDoble AS DECIMAL(10,2)  = NULL,
		@tipo AS CHAR(1) = NULL,
		@descripcion AS VARCHAR(200)  = NULL
	)	
AS
BEGIN	
	SET NOCOUNT ON	
	
	-- "Constantes" con factores de costo de horas extra y doble
	DECLARE @FactorHoraExtra AS DECIMAL(10,2)
	DECLARE @FactorHoraDoble AS DECIMAL(10,2)
	SELECT	@FactorHoraExtra = 1.5, 
			@FactorHoraDoble = 2
			
	DECLARE @CostoAyudantexHora AS MONEY
	DECLARE @CostoOperarioxHora AS MONEY
	DECLARE @CostoMaquinaxHora AS MONEY
	
	DECLARE @CostoHorasRegular AS MONEY
	DECLARE @CostoHorasExtra AS MONEY
	DECLARE @CostoHorasDoble AS MONEY	
	DECLARE @CostoMaquina AS MONEY
	
	-- Extrae los costos por hora de las tablas catalogos	
	SELECT	@CostoAyudantexHora = CostoxHora FROM Lit_Operario WHERE Id = @ayudanteId	
	SELECT	@CostoOperarioxHora = CostoxHora FROM Lit_Operario WHERE Id = @operarioId	
	SELECT	@CostoMaquinaxHora  = CostoxHora FROM Lit_Maquina  WHERE Id = @maquinaId
		
	-- Calcula totales
	SET @CostoHorasRegular = ISNULL(@horasRegular * @CostoAyudantexHora,0) + 
							 ISNULL(@horasRegular * @CostoOperarioxHora,0)
								 
	SET @CostoHorasExtra =	 ISNULL(@horasExtra * @CostoAyudantexHora * @FactorHoraExtra, 0) + 
							 ISNULL(@horasExtra * @CostoOperarioxHora * @FactorHoraExtra, 0)
	
	SET @CostoHorasDoble =	 ISNULL(@horasDoble * @CostoAyudantexHora * @FactorHoraDoble, 0) + 
							 ISNULL(@horasDoble * @CostoOperarioxHora * @FactorHoraDoble, 0)
	
	SET @CostoMaquina = @CostoMaquinaxHora * (ISNULL(@horasRegular,0) + ISNULL(@horasExtra,0) + ISNULL(@horasDoble,0))
	
		
	INSERT INTO Lit_OTDetalle
		(OTId, Fecha, OperarioId, AyudanteId, MaquinaId, Cantidad, HorasRegular, HorasExtra, HorasDoble, Tipo, Descripcion,
		 CostoAyudantexHora, CostoOperarioxHora, CostoMaquinaxHora, CostoHorasRegular, CostoHorasExtra, CostoHorasDoble, CostoMaquina)
	VALUES 
		(@OTId, @fecha, @operarioId, @ayudanteId, @maquinaId, @cantidad, @horasRegular, @horasExtra, @horasDoble, @tipo, @descripcion,
		 @CostoAyudantexHora, @CostoOperarioxHora, @CostoMaquinaxHora, @CostoHorasRegular, @CostoHorasExtra, @CostoHorasDoble, @CostoMaquina) 
	
	
	SET NOCOUNT OFF
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[ProductoGetList]    Script Date: 03/24/2009 22:35:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[ProductoGetList]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[ProductoGetList] 	
	(	
		@empresaId AS INT,
		@sucursalId AS INT,				
		@id AS VARCHAR(12) = NULL,
		@filtro AS VARCHAR(50) = NULL		
	)	
AS
BEGIN	
	SET NOCOUNT ON
	
	SELECT	A.art_id AS Id, art_nombre AS Nombre
	FROM	Articulo A
	INNER JOIN Articulo_x_Bodega AB 
		ON	A.emp_id = AB.emp_id 
		AND A.art_id = AB.art_id 	
	INNER JOIN lit_parametrosbodega P
		ON  P.EmpresaId = AB.Emp_id
		AND P.SucursalId = AB.Suc_id
		AND P.BodegaProductoTerminado = AB.Bod_id
	WHERE P.EmpresaId = @EmpresaId
	  AND P.SucursalId = @SucursalId
	  AND A.Art_Nombre like ''%'' + ISNULL(@filtro,'''') + ''%''
	  AND A.Art_Id = ISNULL(@Id, A.Art_Id) 
	  AND A.Estatus_id = 1
	ORDER BY art_nombre 
	
	SET NOCOUNT OFF
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[RegistroOTMaterialSave]    Script Date: 03/24/2009 22:35:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[RegistroOTMaterialSave]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[RegistroOTMaterialSave] 	
	(			
		@OTId AS INT,
		@empresaId AS INT,
		@fecha AS DATETIME,		
		@operarioId AS INT = NULL,
		@tipo AS CHAR(1) = NULL,
		@articuloId AS VARCHAR(13) = NULL,
		@cantidad AS DECIMAL(10,2)  = NULL,
		@departamento AS VARCHAR(100)  = NULL
	)	
AS
BEGIN	
	SET NOCOUNT ON	
	
	DECLARE @CostoArticuloUnitario AS MONEY
	DECLARE @CostoArticuloTotal AS MONEY
	
	SELECT @CostoArticuloUnitario = art_costo_actual, @CostoArticuloTotal = art_costo_actual * @cantidad
	FROM Articulo
	WHERE Emp_Id = @empresaId
	AND	  Art_id = @articuloId

		
	INSERT INTO Lit_OTMaterial
		(OTId, Fecha, ArticuloId, Tipo, Cantidad, OperarioId, Departamento, CostoArticuloUnitario, CostoArticuloTotal)
	VALUES 
		(@OTId, @fecha, @articuloId, @tipo, @cantidad, @operarioId, @departamento, @CostoArticuloUnitario, @CostoArticuloTotal)
	
	SET NOCOUNT OFF
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[applicationGetId]    Script Date: 03/24/2009 22:35:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[applicationGetId]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[applicationGetId]
	-- Add the parameters for the stored procedure here
	@app_name VARCHAR(50),
	@company_id INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @app_id INT
    -- Insert statements for procedure here
	SELECT @app_id = lit_aplicacion_id
	FROM lit_aplicacion
	WHERE lit_empresa_id = @company_id
	AND lit_aplicacion_nombre = @app_name;

	SELECT @app_id;
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[companyGetByAppName]    Script Date: 03/24/2009 22:35:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[companyGetByAppName]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[companyGetByAppName] 
	-- Add the parameters for the stored procedure here
	@app_name VARCHAR(50) 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT e.Id AS lit_empresa_id, nombre AS lit_empresa_nombre
	FROM ld_general..empresas e
	JOIN lit_aplicacion a ON e.id = a.lit_empresa_id
	WHERE lit_aplicacion_nombre = @app_name
	ORDER BY e.nombre ASC;
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[rightFindByUser]    Script Date: 03/24/2009 22:35:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[rightFindByUser]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[rightFindByUser]
	-- Add the parameters for the stored procedure here
	@usr_name VARCHAR(50), 
	@app_id INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @usr_id INT;

	SELECT @usr_id = lit_usuario_id
	FROM lit_usuario
	WHERE lit_usuario_username = @usr_name
	AND lit_aplicacion_id = @app_id;

	IF (@usr_id IS NOT NULL) BEGIN
		SELECT DISTINCT p.*
		FROM lit_privilegio p
		JOIN lit_rol_privilegio rp ON p.lit_privilegio_id = rp.lit_privilegio_id
		JOIN lit_rol r ON r.lit_rol_id = rp.lit_rol_id
		JOIN lit_usuario_rol ur ON r.lit_rol_id = ur.lit_rol_id
		WHERE ur.lit_usuario_id = @usr_id;
	
	END

  END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[usersGetInRole]    Script Date: 03/24/2009 22:35:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[usersGetInRole]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Ricardo Sosa
-- Create date: 03/04/2009
-- Description:	Finds the users that match the search criteria and belong to the specified role
-- =============================================
CREATE PROCEDURE [dbo].[usersGetInRole]
	@role_name VARCHAR(50),
	@app_id INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @role_id INT;

	SELECT @role_id = lit_rol_id
	FROM lit_rol
	WHERE lit_rol_nombre = @role_name
	AND lit_aplicacion_id = @app_id;
	
	IF (@role_id IS NOT NULL) BEGIN
		SELECT u.* 
		FROM lit_usuario u
		JOIN lit_usuario_rol ur ON u.lit_usuario_id = ur.lit_usuario_id
		WHERE ur.lit_rol_id = @role_id
		ORDER BY lit_usuario_username;
	END
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[userIsInRole]    Script Date: 03/24/2009 22:35:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[userIsInRole]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Ricardo Sosa
-- Create date: 03/04/2009
-- Description:	Finds the users that match the search criteria and belong to the specified role
-- =============================================
CREATE PROCEDURE [dbo].[userIsInRole]
	@user_name VARCHAR(50),
	@role_name VARCHAR(50),
	@app_id INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @rol_id INT;
	DECLARE @usr_id INT;

	SELECT @rol_id = lit_rol_id
	FROM lit_rol
	WHERE lit_rol_nombre = @role_name
	AND lit_aplicacion_id = @app_id;

	SELECT @usr_id = lit_usuario_id
	FROM lit_usuario
	WHERE lit_usuario_username = @user_name
	AND lit_aplicacion_id = @app_id;
	
	IF ((@rol_id IS NOT NULL) AND (@usr_id IS NOT NULL)) BEGIN
		IF (EXISTS (SELECT * FROM lit_usuario_rol WHERE lit_usuario_id = @usr_id AND lit_rol_id = @rol_id))
			SELECT 1;
		ELSE SELECT 0;
	END
	ELSE BEGIN
		SELECT -1;
	END
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[userRoleDelete]    Script Date: 03/24/2009 22:35:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[userRoleDelete]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Ricardo
-- Create date: 04/03/2009
-- Description:	Deletes a role from a user
-- =============================================
CREATE PROCEDURE [dbo].[userRoleDelete] 
	@user_name VARCHAR(50),
	@role_name VARCHAR(50),
	@app_id INT
AS	
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @rol_id INT;
	DECLARE @usr_id INT;

	SELECT @rol_id = lit_rol_id
	FROM lit_rol
	WHERE lit_rol_nombre = @role_name
	AND lit_aplicacion_id = @app_id;

	SELECT @usr_id = lit_usuario_id
	FROM lit_usuario
	WHERE lit_usuario_username = @user_name
	AND lit_aplicacion_id = @app_id;

	IF ((@rol_id IS NOT NULL) AND (@usr_id IS NOT NULL))
	BEGIN
		DELETE FROM lit_usuario_rol 
		WHERE lit_rol_id = @rol_id 
		AND lit_usuario_id = @usr_id;
		SELECT 0;
	END
	ELSE BEGIN
		SELECT 1; 
	END
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[userRoleAdd]    Script Date: 03/24/2009 22:35:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[userRoleAdd]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Ricardo Sosa>
-- Create date: <03/03/09>
-- Description:	<Agrega un rol a un usuario>
-- =============================================
CREATE PROCEDURE [dbo].[userRoleAdd]
	-- Add the parameters for the stored procedure here
	@UserName VARCHAR(50),
	@RoleName VARCHAR(50),
	@ApplicationId INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @role_id AS INT;
	DECLARE @user_id AS INT;

	SELECT @role_id = lit_rol_id  
	FROM lit_rol
	WHERE lit_aplicacion_id = @ApplicationId
	AND lit_rol_nombre = @RoleName;

	SELECT @user_id = lit_usuario_id  
	FROM lit_usuario
	WHERE lit_aplicacion_id = @ApplicationId
	AND lit_usuario_username = @UserName;

	IF (NOT EXISTS(	SELECT * 
					FROM lit_usuario_rol 
					WHERE lit_usuario_id = @user_id 
					AND lit_rol_id = @role_id)) BEGIN
		
		INSERT INTO lit_usuario_rol(lit_usuario_id, lit_rol_id) VALUES (@user_id, @role_id);
	END
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[roleFindByUser]    Script Date: 03/24/2009 22:35:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[roleFindByUser]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Ricardo Sosa
-- Create date: 07/03/2009
-- Description:	Gets the roles of the specified user
-- =============================================
CREATE PROCEDURE [dbo].[roleFindByUser]
	@usr_name VARCHAR(50),
	@app_id INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @usr_id INT;

	SELECT @usr_id = lit_usuario_id
	FROM lit_usuario
	WHERE lit_usuario_username = @usr_name
	AND lit_aplicacion_id = @app_id;

	IF (@usr_id IS NOT NULL) BEGIN

		SELECT r.* 
		FROM lit_rol r
		JOIN lit_usuario_rol ur ON ur.lit_rol_id = r.lit_rol_id
		WHERE r.lit_aplicacion_id = @app_id
		AND ur.lit_usuario_id = @usr_id
		ORDER BY r.lit_rol_nombre;

	END
	
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[userDelete]    Script Date: 03/24/2009 22:35:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[userDelete]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[userDelete]
	-- Add the parameters for the stored procedure here
	@user_id INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DELETE FROM lit_usuario_rol WHERE lit_usuario_id = @user_id;
	DELETE FROM lit_usuario WHERE lit_usuario_id = @user_id;

END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[userFindInRole]    Script Date: 03/24/2009 22:35:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[userFindInRole]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Ricardo Sosa
-- Create date: 03/04/2009
-- Description:	Finds the users that match the search criteria and belong to the specified role
-- =============================================
CREATE PROCEDURE [dbo].[userFindInRole]
	@role_name VARCHAR(50),
	@user_match VARCHAR(50),
	@app_id INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @role_id INT;

	DECLARE @full_match VARCHAR(52)
	SET @full_match = ''%'' + @user_match + ''%'';

	SELECT @role_id = lit_rol_id
	FROM lit_rol
	WHERE lit_rol_nombre = @role_name
	AND lit_aplicacion_id = @app_id;
	
    SELECT * 
	FROM lit_usuario
	WHERE lit_usuario_id IN (SELECT lit_usuario_id 
							FROM lit_usuario_rol 
							WHERE lit_rol_id = @role_id)
	AND lit_usuario_username like @full_match
	ORDER BY lit_usuario_username;

END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[roleDelete]    Script Date: 03/24/2009 22:35:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[roleDelete]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Ricardo
-- Create date: 04/03/2009
-- Description:	Deletes a role from the database
-- =============================================
CREATE PROCEDURE [dbo].[roleDelete] 
	@role_name VARCHAR(50),
	@app_id INT,
	@throw BIT
AS	
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @role_id INT;

	SELECT @role_id = lit_rol_id
	FROM lit_rol
	WHERE lit_rol_nombre = @role_name 
	AND lit_aplicacion_id = @app_id;

	IF (@role_id IS NOT NULL) BEGIN
		IF EXISTS(	SELECT lit_rol_id 
					FROM lit_usuario_rol 
					WHERE lit_rol_id = @role_id ) BEGIN
			-- Hay usuarios asociados al rol
			IF (@throw = 1) BEGIN
				SELECT 0;
			END
			ELSE BEGIN
				DELETE FROM lit_usuario_rol WHERE lit_rol_id = @role_id;
				DELETE FROM lit_rol_privilegio WHERE lit_rol_id = @role_id;
				DELETE FROM lit_rol WHERE lit_rol_id = @role_id;	
				SELECT 1;
			END
						
		END
		ELSE BEGIN
			DELETE FROM lit_rol_privilegio WHERE lit_rol_id = @role_id;
			DELETE FROM lit_rol WHERE lit_rol_id = @role_id;	
			SELECT 1;
		END
	END
	ELSE BEGIN
		SELECT 1;
	END
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[rolesFromUserDelete]    Script Date: 03/24/2009 22:35:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[rolesFromUserDelete]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[rolesFromUserDelete] 
	-- Add the parameters for the stored procedure here
	@user_id INT
AS
BEGIN
	SET NOCOUNT ON;

	-- Insert statements for procedure here
	IF (EXISTS(	SELECT * 
					FROM lit_usuario
					WHERE lit_usuario_id = @user_id)) 
	BEGIN
		DELETE FROM lit_usuario_rol WHERE lit_usuario_id = @user_id;
		SELECT 1;
	END
	ELSE SELECT 0;
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[userRoleAddWithIds]    Script Date: 03/24/2009 22:35:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[userRoleAddWithIds]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[userRoleAddWithIds]
	-- Add the parameters for the stored procedure here
	@user_id INT, 
	@role_id INT
AS
BEGIN
	SET NOCOUNT ON;

	-- Insert statements for procedure here
	IF (NOT EXISTS(	SELECT * 
					FROM lit_usuario_rol
					WHERE lit_rol_id = @role_id
					AND lit_usuario_id = @user_id)) 
	BEGIN
		INSERT INTO lit_usuario_rol (lit_usuario_id, lit_rol_id)
		VALUES (@user_id, @role_id);
	END
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[rightToRoleAdd]    Script Date: 03/24/2009 22:35:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[rightToRoleAdd]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[rightToRoleAdd] 
	-- Add the parameters for the stored procedure here
	@right_id INT, 
	@role_id INT
AS
BEGIN
	SET NOCOUNT ON;

	-- Insert statements for procedure here
	IF (NOT EXISTS(	SELECT * 
					FROM lit_rol_privilegio
					WHERE lit_rol_id = @role_id
					AND lit_privilegio_id = @right_id)) 
	BEGIN
		INSERT into lit_rol_privilegio (lit_rol_id, lit_privilegio_id)
		VALUES (@role_id, @right_id);
	END
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[rightFindByRole]    Script Date: 03/24/2009 22:35:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[rightFindByRole]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[rightFindByRole]
	-- Add the parameters for the stored procedure here
	@role_name VARCHAR(50), 
	@app_id INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @rol_id INT;

	SELECT @rol_id = lit_rol_id
	FROM lit_rol
	WHERE lit_rol_nombre = @role_name
	AND lit_aplicacion_id = @app_id;

    -- Insert statements for procedure here
	SELECT *
	FROM lit_privilegio p 
	JOIN lit_rol_privilegio rp ON p.lit_privilegio_id = rp.lit_privilegio_id
	WHERE rp.lit_rol_id = @rol_id;
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[rightFromRoleDelete]    Script Date: 03/24/2009 22:35:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[rightFromRoleDelete]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[rightFromRoleDelete] 
	-- Add the parameters for the stored procedure here
	@role_id INT
AS
BEGIN
	SET NOCOUNT ON;

	-- Insert statements for procedure here
	IF (EXISTS(	SELECT * 
					FROM lit_rol
					WHERE lit_rol_id = @role_id)) 
	BEGIN
		DELETE FROM lit_rol_privilegio WHERE lit_rol_id = @role_id;
		SELECT 1;
	END
	ELSE SELECT 0;
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[RegistroOTGastoSave]    Script Date: 03/24/2009 22:35:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[RegistroOTGastoSave]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[RegistroOTGastoSave] 	
	(	
		@OTId AS INT,
		@gastoId AS INT  = NULL,
		@monto AS DECIMAL(10,2) = NULL
	)	
AS
BEGIN	
	SET NOCOUNT ON	
		
	INSERT INTO Lit_OTGasto
		(OTId, GastoId, Monto)
	VALUES 
		(@OTId, @gastoId, @monto)
	
	SET NOCOUNT OFF
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[OperarioGetList]    Script Date: 03/24/2009 22:35:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[OperarioGetList]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[OperarioGetList] 	
	(	
		@empresaId AS INT,		
		@id AS VARCHAR(12) = NULL,
		@filtro AS VARCHAR(50) = NULL		
	)	
AS
BEGIN	
	SET NOCOUNT ON
	
	SELECT Id, Nombre
	FROM Lit_Operario
	WHERE EmpresaId = @empresaId	  
	  AND Nombre like ''%'' + ISNULL(@filtro,'''') + ''%''
	  AND Id = ISNULL(@Id, Id)  
	ORDER BY Nombre
	
	SET NOCOUNT OFF
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[rightFindByType]    Script Date: 03/24/2009 22:35:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[rightFindByType]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[rightFindByType]
	@app_id INT,
	@type VARCHAR(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT *
	FROM lit_privilegio
	WHERE lit_aplicacion_id = @app_id
	AND lit_privilegio_tipo = @type
	ORDER BY lit_privilegio_nombre;
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[roleAdd]    Script Date: 03/24/2009 22:35:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[roleAdd]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[roleAdd] 
	-- Add the parameters for the stored procedure here
	@role_name VARCHAR(50),
	@role_desc VARCHAR(255),
	@app_id INT
AS	
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	IF EXISTS(	SELECT lit_rol_id 
				FROM lit_rol 
				WHERE lit_rol_nombre = @role_name 
				AND lit_aplicacion_id = @app_id )
	BEGIN
		SELECT 0;
	END
    ELSE BEGIN
		INSERT INTO lit_rol (lit_rol_nombre, lit_rol_desc, lit_aplicacion_id)
		VALUES (@role_name, @role_desc, @app_id)
		SELECT @@Identity;
	END
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[roleFindByName]    Script Date: 03/24/2009 22:35:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[roleFindByName]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Ricardo Sosa
-- Create date: 09/03/2009
-- Description:	Gets the role information by name
-- =============================================
CREATE PROCEDURE [dbo].[roleFindByName] 
	-- Add the parameters for the stored procedure here
	@role_name VARCHAR(50),
	@app_id INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT *
	FROM lit_rol
	WHERE lit_rol_nombre = @role_name
	AND lit_aplicacion_id = @app_id;
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[roleFindAll]    Script Date: 03/24/2009 22:35:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[roleFindAll]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[roleFindAll]
	@app_id INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT * 
	FROM lit_rol 
	WHERE lit_aplicacion_id = @app_id
	ORDER BY lit_rol_nombre;
	
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[roleUpdate]    Script Date: 03/24/2009 22:35:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[roleUpdate]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[roleUpdate] 
	-- Add the parameters for the stored procedure here
	@role_name VARCHAR(50), 
	@role_desc VARCHAR(255),
	@role_id INT
AS
BEGIN
	SET NOCOUNT ON;

	-- Insert statements for procedure here
	IF (EXISTS(	SELECT * 
					FROM lit_rol
					WHERE lit_rol_id = @role_id)) 
	BEGIN
		UPDATE lit_rol
		SET lit_rol_nombre = @role_name,
			lit_rol_desc = @role_desc
		WHERE lit_rol_id = @role_id;
		SELECT 1;
	END
	ELSE SELECT 0;
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[userUpdate]    Script Date: 03/24/2009 22:35:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[userUpdate]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[userUpdate] 
	-- Add the parameters for the stored procedure here
	@user_name VARCHAR(50), 
	@user_pwd VARCHAR(50),
	@first_name VARCHAR(50), 
	@last_name VARCHAR(50),
	@user_id INT
AS
BEGIN
	SET NOCOUNT ON;

	-- Insert statements for procedure here
	IF (EXISTS(	SELECT * 
					FROM lit_usuario
					WHERE lit_usuario_id = @user_id)) 
	BEGIN
		UPDATE lit_usuario
		SET lit_usuario_username = @user_name,
			lit_usuario_password = @user_pwd,
			lit_usuario_nombre = @first_name,
			lit_usuario_apellido = @last_name
		WHERE lit_usuario_id = @user_id;
		SELECT 1;
	END
	ELSE SELECT 0;
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[userFindByName]    Script Date: 03/24/2009 22:35:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[userFindByName]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[userFindByName] 
	-- Add the parameters for the stored procedure here
	@username VARCHAR(50), 
	@app_id INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT *
	FROM lit_usuario
	WHERE lit_usuario_username = @username
	AND lit_aplicacion_id = @app_id;
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[userAdd]    Script Date: 03/24/2009 22:35:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[userAdd]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[userAdd] 
	-- Add the parameters for the stored procedure here
	@user_first_name VARCHAR(50),
	@user_last_name VARCHAR(50),
	@user_name VARCHAR(50),
	@user_pwd VARCHAR(50),
	@app_id INT
AS	
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	IF EXISTS(	SELECT lit_usuario_id 
				FROM lit_usuario 
				WHERE lit_usuario_username = @user_name 
				AND lit_aplicacion_id = @app_id )
	BEGIN
		SELECT 0;
	END
    ELSE BEGIN
		INSERT INTO lit_usuario (lit_usuario_username, lit_usuario_password, lit_usuario_nombre, lit_usuario_apellido, lit_aplicacion_id)
		VALUES (@user_name, @user_pwd, @user_first_name, @user_last_name, @app_id)
		SELECT @@Identity;
	END
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[userFindAll]    Script Date: 03/24/2009 22:35:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[userFindAll]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[userFindAll]
	-- Add the parameters for the stored procedure here
	@app_id INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT *
	FROM lit_usuario
	WHERE lit_aplicacion_id = @app_id
	ORDER BY lit_usuario_username;;
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[ClienteGetList]    Script Date: 03/24/2009 22:35:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[ClienteGetList]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[ClienteGetList] 	
	(	
		@empresaId AS INT,		
		@id AS VARCHAR(12) = NULL,
		@filtro AS VARCHAR(50) = NULL		
	)	
AS
BEGIN	
	SET NOCOUNT ON
	
	SELECT Cliente_Id AS Id, Cliente_Nombre AS Nombre
	FROM Cliente
	WHERE Emp_Id = @empresaId	  
	  AND Cliente_Nombre like ''%'' + ISNULL(@filtro,'''') + ''%''
	  AND Cliente_Id = ISNULL(@Id, Cliente_Id)
	  AND Cliente_Activo = ''si'' 

	SET NOCOUNT OFF
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[branchGetByCompany]    Script Date: 03/24/2009 22:35:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[branchGetByCompany]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[branchGetByCompany]
	-- Add the parameters for the stored procedure here
	@company_id INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT convert(int,suc_Id) AS lit_sucursal_id, suc_nombre AS lit_sucursal_nombre, convert(int,emp_id) AS lit_empresa_id 
	FROM sucursal
	WHERE emp_id = @company_id;
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[OrdenTrabajoParametersGet]    Script Date: 03/24/2009 22:35:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[OrdenTrabajoParametersGet]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[OrdenTrabajoParametersGet]
	(	
		@EmpresaId	AS INT,
		@SucursalId	AS INT
	)	
AS
BEGIN	
	SET NOCOUNT ON

	-- Lista de tablas a ser devueltas a la aplicacion para cargar la hoja de registro	
	SELECT 0 AS TableId, ''Catalog''		AS TableName	UNION
	SELECT 1 AS TableId, ''Producto''		AS TableName	UNION
	SELECT 2 AS TableId, ''Material''		AS TableName	UNION
	SELECT 3 AS TableId, ''Operario''		AS TableName    UNION
	SELECT 4 AS TableId, ''Maquina''		AS TableName    UNION		
	SELECT 5 AS TableId, ''Gasto''		AS TableName    --UNION		


	-- Producto
	SELECT	A.art_id AS Codigo, art_nombre AS Nombre
	FROM	Articulo A
	INNER JOIN Articulo_x_Bodega AB 
		ON	A.emp_id = AB.emp_id 
		AND A.art_id = AB.art_id 	
	INNER JOIN lit_parametrosbodega P
		ON  P.EmpresaId = AB.Emp_id
		AND P.SucursalId = AB.Suc_id
		AND P.BodegaProductoTerminado = AB.Bod_id
	WHERE P.EmpresaId = @EmpresaId
	  AND P.SucursalId = @SucursalId	 
	  AND A.Estatus_id = 1
	ORDER BY 2  


	-- Materia Prima
	SELECT	A.art_id AS Codigo, art_nombre AS Nombre
	FROM	Articulo A
	INNER JOIN Articulo_x_Bodega AB 
		ON	A.emp_id = AB.emp_id 
		AND A.art_id = AB.art_id 	
	INNER JOIN lit_parametrosbodega P
		ON  P.EmpresaId = AB.Emp_id
		AND P.SucursalId = AB.Suc_id
		AND P.BodegaMateriaPrima = AB.Bod_id
	WHERE P.EmpresaId = @EmpresaId
	  AND P.SucursalId = @SucursalId	 
	  AND A.Estatus_id = 1
	ORDER BY 2
	 
	-- Operario
	SELECT	Id, Nombre
	FROM	Lit_Operario
	WHERE	EmpresaId = @EmpresaId

	-- Maquina
	SELECT	Id, Nombre
	FROM	Lit_Maquina
	WHERE	EmpresaId = @EmpresaId

	-- Gasto 
	Exec GastoGet @EmpresaId, NULL
	
	SET NOCOUNT OFF
END
' 
END
GO


DECLARE @EmpresaId AS INT
SET @EmpresaId = 9

INSERT INTO Lit_Aplicacion 
(lit_aplicacion_nombre, lit_aplicacion_desc, lit_empresa_id)
VALUES
('Litografia', 'Control de Ordenes de Trabajo', @EmpresaId)


INSERT INTO Lit_ParametrosBodega
(EmpresaId, SucursalId, BodegaMateriaPrima, BodegaProductoEnProceso, BodegaProductoTerminado)
VALUES
(@EmpresaId, 1, 1, 2, 3)


declare @mantId as int
declare @procId as int
declare @repId as int

INSERT INTO lit_privilegio (lit_privilegio_nombre, lit_privilegio_desc, lit_privilegio_padre_id, lit_privilegio_tipo, lit_aplicacion_id) VALUES ('Mantenimientos','Mantenimientos del sistema',NULL,'menu',1)
set @mantId = scope_identity()
INSERT INTO lit_privilegio (lit_privilegio_nombre, lit_privilegio_desc, lit_privilegio_padre_id, lit_privilegio_tipo, lit_aplicacion_id) VALUES ('Terminar Orden de Trabajo','Terminar Orden de Trabajo',NULL,'permiso',1)
INSERT INTO lit_privilegio (lit_privilegio_nombre, lit_privilegio_desc, lit_privilegio_padre_id, lit_privilegio_tipo, lit_aplicacion_id) VALUES ('Ingresar gastos','Permite ingresar gastos indirectos, directos y mano de obra indirecta',NULL,'permiso',1)
INSERT INTO lit_privilegio (lit_privilegio_nombre, lit_privilegio_desc, lit_privilegio_padre_id, lit_privilegio_tipo, lit_aplicacion_id) VALUES ('Ver OT completa','Permite visualizar OT completa',NULL,'permiso',1)
INSERT INTO lit_privilegio (lit_privilegio_nombre, lit_privilegio_desc, lit_privilegio_padre_id, lit_privilegio_tipo, lit_aplicacion_id) VALUES ('Reportes','Reportes del Sistema',NULL,'menu',1)
set @repId = scope_identity()
INSERT INTO lit_privilegio (lit_privilegio_nombre, lit_privilegio_desc, lit_privilegio_padre_id, lit_privilegio_tipo, lit_aplicacion_id) VALUES ('Procesos','Procesos del sistema',NULL,'menu',1)
set @procId = scope_identity()
INSERT INTO lit_privilegio (lit_privilegio_nombre, lit_privilegio_desc, lit_privilegio_padre_id, lit_privilegio_tipo, lit_aplicacion_id) VALUES ('Gastos','Mant de Gastos',@mantId,'menu',1)
INSERT INTO lit_privilegio (lit_privilegio_nombre, lit_privilegio_desc, lit_privilegio_padre_id, lit_privilegio_tipo, lit_aplicacion_id) VALUES ('Puestos','Mant de Puestos',@mantId,'menu',1)
INSERT INTO lit_privilegio (lit_privilegio_nombre, lit_privilegio_desc, lit_privilegio_padre_id, lit_privilegio_tipo, lit_aplicacion_id) VALUES ('Operarios','Mant de Operarios',@mantId,'menu',1)
INSERT INTO lit_privilegio (lit_privilegio_nombre, lit_privilegio_desc, lit_privilegio_padre_id, lit_privilegio_tipo, lit_aplicacion_id) VALUES ('Maquinas','Mant de Maquinas',@mantId,'menu',1)
INSERT INTO lit_privilegio (lit_privilegio_nombre, lit_privilegio_desc, lit_privilegio_padre_id, lit_privilegio_tipo, lit_aplicacion_id) VALUES ('Roles','Mant de Roles',@mantId,'menu',1)
INSERT INTO lit_privilegio (lit_privilegio_nombre, lit_privilegio_desc, lit_privilegio_padre_id, lit_privilegio_tipo, lit_aplicacion_id) VALUES ('Usuarios','Mant de Usuarios',@mantId,'menu',1)
INSERT INTO lit_privilegio (lit_privilegio_nombre, lit_privilegio_desc, lit_privilegio_padre_id, lit_privilegio_tipo, lit_aplicacion_id) VALUES ('Bodegas','Parametros de Bodegas',@mantId,'menu',1)
INSERT INTO lit_privilegio (lit_privilegio_nombre, lit_privilegio_desc, lit_privilegio_padre_id, lit_privilegio_tipo, lit_aplicacion_id) VALUES ('Hoja de Registro','Hoja de registro de Orden de Trabajo',@procId,'menu',1)
INSERT INTO lit_privilegio (lit_privilegio_nombre, lit_privilegio_desc, lit_privilegio_padre_id, lit_privilegio_tipo, lit_aplicacion_id) VALUES ('Conceptos de Orden de Trabajo','Conceptos de orden de trabajo resumido',@repId,'menu',1)
INSERT INTO lit_privilegio (lit_privilegio_nombre, lit_privilegio_desc, lit_privilegio_padre_id, lit_privilegio_tipo, lit_aplicacion_id) VALUES ('Reporte de horas laboradas','Reporte de horas invertidas y salario de cada operario',@repId,'menu',1)


insert into lit_rol (lit_rol_nombre, lit_rol_desc, lit_aplicacion_id)
values ('Administrador', 'Administrador del sistema', 1)


declare @privMantId as int
declare @privMantUsuarioId as int
declare @privMantRolesId as int

select @privMantId = lit_privilegio_id
from lit_privilegio
where lit_privilegio_nombre = 'Mantenimientos'

select @privMantUsuarioId = lit_privilegio_id
from lit_privilegio
where lit_privilegio_nombre = 'Usuarios'

select @privMantRolesId = lit_privilegio_id
from lit_privilegio
where lit_privilegio_nombre = 'Roles'

insert into lit_rol_privilegio values (1,@privMantId)
insert into lit_rol_privilegio values (1,@privMantUsuarioId)
insert into lit_rol_privilegio values (1,@privMantRolesId)


insert into lit_usuario (lit_usuario_username, lit_usuario_password, lit_usuario_nombre, lit_usuario_apellido, lit_aplicacion_id)
values ('admin', 'YWRtaW4=', 'Usuario administrador', 'Sossa', 1)  -- Clave es la misma que el usuario

insert into lit_usuario_rol (lit_usuario_id, lit_rol_id) values(1,1)


if not exists(select 1 from sucursal where emp_id = @EmpresaId) 
BEGIN
	INSERT INTO sucursal (emp_id, suc_id, suc_nombre) VALUES (@EmpresaId,1,'Litografía')
END



/*
select * from lit_privilegio
select * from dbo.lit_rol
select * from dbo.lit_rol_privilegio
select * from dbo.lit_usuario
select * from dbo.lit_usuario_rol

*/